﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using NUnit.Framework;
using ServiceStack.Text;

namespace ServiceStack.OrmLite.Tests.Issues;

[TestFixtureOrmLite]
public class MultipleSelfJoinsWithJoinAliases(DialectContext context) : OrmLiteProvidersTestBase(context)
{
    private static Sale PopulateData(IDbConnection db, Guid tenantId)
    {
        db.DropTable<Sale>();
        db.DropTable<ContactIssue>();

        db.CreateTable<ContactIssue>();
        db.CreateTable<Sale>();

        var buyer = new ContactIssue
        {
            Id = Guid.NewGuid(),
            TenantId = tenantId,
            FirstName = "BuyerFirst",
            LastName = "LastBuyer"
        };

        var seller = new ContactIssue
        {
            Id = Guid.NewGuid(),
            TenantId = tenantId,
            FirstName = "SellerFirst",
            LastName = "LastSeller"
        };

        db.Insert(buyer, seller);

        var sale = new Sale
        {
            Id = Guid.NewGuid(),
            TenantId = tenantId,
            BuyerId = buyer.Id,
            SellerId = seller.Id,
            AmountCents = 100,
        };

        db.Insert(sale);
        return sale;
    }

    [Test]
    public void Can_use_custom_SqlExpression_to_add_multiple_self_Left_Joins()
    {
        using var db = OpenDbConnection();
        var tenantId = Guid.NewGuid();
        var sale = PopulateData(db, tenantId);

        var q = db.From<Sale>()
            .CustomJoin("LEFT JOIN {0} seller on (Sale.{1} = seller.Id)"
                .Fmt("ContactIssue".SqlTable(DialectProvider), "SellerId".SqlColumn(DialectProvider)))
            .CustomJoin("LEFT JOIN {0} buyer on (Sale.{1} = buyer.Id)"
                .Fmt("ContactIssue".SqlTable(DialectProvider), "BuyerId".SqlColumn(DialectProvider)))
            .Select(@"Sale.*
                        , buyer.{0} AS BuyerFirstName
                        , buyer.{1} AS BuyerLastName
                        , seller.{0} AS SellerFirstName
                        , seller.{1} AS SellerLastName"
                .Fmt("FirstName".SqlColumn(DialectProvider), "LastName".SqlColumn(DialectProvider)));

        q.Where(x => x.TenantId == tenantId);

        var sales = db.Select<SaleView>(q);
        Assert.That(sales.Count, Is.EqualTo(1));

        //Alternative
        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select<Sale, ContactIssue>((s, c) => new
            {
                s,
                BuyerFirstName = Sql.TableAlias(c.FirstName, "buyer"),
                BuyerLastName = Sql.TableAlias(c.LastName, "buyer"),
                SellerFirstName = Sql.TableAlias(c.FirstName, "seller"),
                SellerLastName = Sql.TableAlias(c.LastName, "seller"),
            });

        q.Where(x => x.TenantId == tenantId);

        sales = db.Select<SaleView>(q);
        Assert.That(sales.Count, Is.EqualTo(1));


        var salesView = sales[0];

        //salesView.PrintDump();

        Assert.That(salesView.Id, Is.EqualTo(sale.Id));
        Assert.That(salesView.TenantId, Is.EqualTo(sale.TenantId));
        Assert.That(salesView.AmountCents, Is.EqualTo(sale.AmountCents));
        Assert.That(salesView.BuyerFirstName, Is.EqualTo("BuyerFirst"));
        Assert.That(salesView.BuyerLastName, Is.EqualTo("LastBuyer"));
        Assert.That(salesView.SellerFirstName, Is.EqualTo("SellerFirst"));
        Assert.That(salesView.SellerLastName, Is.EqualTo("LastSeller"));

        q.Select("seller.*, 0 EOT, buyer.*");

        var multi = db.Select<Tuple<ContactIssue, ContactIssue>>(q);
        multi.PrintDump();

        Assert.That(multi[0].Item1.FirstName, Is.EqualTo("SellerFirst"));
        Assert.That(multi[0].Item2.FirstName, Is.EqualTo("BuyerFirst"));
    }

    [Test]
    public void Can_use_CustomSql()
    {
        var customFmt = "";
        if (Dialect == Dialect.SqlServer || Dialect == Dialect.SqlServer2012)
            customFmt = "CONCAT(LEFT({0}, 1),LEFT({1},1))";
        else if (Dialect == Dialect.Sqlite)
            customFmt = "substr({0}, 1, 1) || substr({1}, 1, 1)";

        if (string.IsNullOrEmpty(customFmt))
            return;

        using var db = OpenDbConnection();
        var tenantId = Guid.NewGuid();
        var sale = PopulateData(db, tenantId);

        var q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select<Sale, ContactIssue>((s, c) => new
            {
                s,
                BuyerFirstName = Sql.TableAlias(c.FirstName, "buyer"),
                BuyerLastName = Sql.TableAlias(c.LastName, "buyer"),
                BuyerInitials = Sql.Custom(customFmt.Fmt("buyer.FirstName", "buyer.LastName")),
                SellerFirstName = Sql.TableAlias(c.FirstName, "seller"),
                SellerLastName = Sql.TableAlias(c.LastName, "seller"),
                SellerInitials = Sql.Custom(customFmt.Fmt("seller.FirstName", "seller.LastName")),
            });

        var sales = db.Select<SaleView>(q);
        var salesView = sales[0];

        Assert.That(salesView.BuyerFirstName, Is.EqualTo("BuyerFirst"));
        Assert.That(salesView.BuyerLastName, Is.EqualTo("LastBuyer"));
        Assert.That(salesView.BuyerInitials, Is.EqualTo("BL"));
        Assert.That(salesView.SellerFirstName, Is.EqualTo("SellerFirst"));
        Assert.That(salesView.SellerLastName, Is.EqualTo("LastSeller"));
        Assert.That(salesView.SellerInitials, Is.EqualTo("SL"));
    }

    void AssertTupleResults(List<Tuple<Sale, ContactIssue, ContactIssue>> results)
    {
        var result = results[0];
        var sales = result.Item1;
        var buyer = result.Item2;
        var seller = result.Item3;

        Assert.That(sales.AmountCents, Is.EqualTo(100));
        Assert.That(buyer.FirstName, Is.EqualTo("BuyerFirst"));
        Assert.That(buyer.LastName, Is.EqualTo("LastBuyer"));
        Assert.That(seller.FirstName, Is.EqualTo("SellerFirst"));
        Assert.That(seller.LastName, Is.EqualTo("LastSeller"));
    }

    [Test]
    [Ignore("Needs review - MONOREPO")]
    public void Can_use_Custom_Select_with_Tuples()
    {
        using var db = OpenDbConnection();
        var tenantId = Guid.NewGuid();
        var sale = PopulateData(db, tenantId);

        var q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select<Sale,ContactIssue>((s,c) => new {
                s, 
                buyer = Sql.TableAlias(c, "buyer"), 
                seller = Sql.TableAlias(c, "seller"),
            });

        AssertTupleResults(db.Select<Tuple<Sale, ContactIssue, ContactIssue>>(q));

        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select<Sale,ContactIssue>((s,c) => new {
                s,
                t1 = Sql.EOT,
                buyer = Sql.TableAlias(c, "buyer"), 
                t2 = Sql.EOT,
                seller = Sql.TableAlias(c, "seller"),
            });

        AssertTupleResults(db.Select<Tuple<Sale, ContactIssue, ContactIssue>>(q));

        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select("Sale.*, 0 EOT, buyer.*, 0 EOT, seller.*, 0 EOT");

        AssertTupleResults(db.Select<Tuple<Sale, ContactIssue, ContactIssue>>(q));

        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"));

        AssertTupleResults(db.SelectMulti<Sale, ContactIssue, ContactIssue>(q, new[] { "Sale.*", "buyer.*", "seller.*" }));
    }

    [Test]
    [Ignore("Needs review - MONOREPO")]
    public async Task Can_use_Custom_Select_with_Tuples_Async()
    {
        using var db = await OpenDbConnectionAsync();
        var tenantId = Guid.NewGuid();
        var sale = PopulateData(db, tenantId);

        var q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select<Sale,ContactIssue>((s,c) => new {
                s, 
                buyer = Sql.TableAlias(c, "buyer"), 
                seller = Sql.TableAlias(c, "seller"),
            });

        AssertTupleResults(await db.SelectAsync<Tuple<Sale, ContactIssue, ContactIssue>>(q));

        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select<Sale,ContactIssue>((s,c) => new {
                s,
                t1 = Sql.EOT,
                buyer = Sql.TableAlias(c, "buyer"), 
                t2 = Sql.EOT,
                seller = Sql.TableAlias(c, "seller"),
            });

        AssertTupleResults(await db.SelectAsync<Tuple<Sale, ContactIssue, ContactIssue>>(q));

        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"))
            .Select("Sale.*, 0 EOT, buyer.*, 0 EOT, seller.*, 0 EOT");

        AssertTupleResults(await db.SelectAsync<Tuple<Sale, ContactIssue, ContactIssue>>(q));

        q = db.From<Sale>()
            .LeftJoin<ContactIssue>((s, c) => s.SellerId == c.Id, db.TableAlias("seller"))
            .LeftJoin<ContactIssue>((s, c) => s.BuyerId == c.Id, db.TableAlias("buyer"));

        AssertTupleResults(await db.SelectMultiAsync<Sale, ContactIssue, ContactIssue>(q, new[] { "Sale.*", "buyer.*", "seller.*" }));
    }
}